<?php

namespace app\api\controller;

use app\common\controller\Api;

use app\common\model\Follow as FollowModel;
use app\common\model\User;
use app\common\service\UserPermissionService;
use think\Db;
use app\common\model\Customer as CustomerModel;
use app\common\model\Admin as AdminModel; // 假设 AdminModel 对应用户表
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date; // 用于处理 Excel 日期
use think\Request;
use app\common\model\Contracts as ContractsModel;

/**
 * 首页接口
 */
class Contracts extends Api
{
    protected $noNeedLogin = [];
    protected $noNeedRight = ['*'];

    public function getStatistics()
    {
        $contracts = new \app\common\model\Contracts();
        $userId = $this->auth->id;

        // 1. 全部
        $whereAll = [];
        $whereAll['c.is_delete']=0;
        $userPermissionService = new UserPermissionService();
        $staffids=$userPermissionService->getUsersByPermission($this->auth->id);
        $whereAll['c.staff_id']=['in',$staffids];
        $totalAll = $contracts->alias('c')->where($whereAll)->count();

        // 2. 我负责的
        $whereMine = $whereAll;
        $whereMine['c.staff_id'] = $userId;
        $whereMine['c.is_delete']=0;
        $totalMine = $contracts->alias('c')->where($whereMine)->count();

        // 3. 下属负责的
        $staffids = Db::name('user')->where('leader_id', $userId)->column('id');
        $whereSub = $whereAll;
        $whereSub['c.is_delete']=0;
        if ($staffids) {
            $whereSub['c.staff_id'] = ['in', $staffids];
            $totalSub = $contracts->alias('c')->where($whereSub)->count();
        } else {
            $totalSub = 0;
        }
        $data = [
            ['type' => 1, 'title' => '全部', 'value' => $totalAll],
            ['type' => 2, 'title' => '我负责的', 'value' => $totalMine],
            ['type' => 3, 'title' => '下属负责的', 'value' => $totalSub],
        ];

        $this->success('success', $data);
    }
    public function getContractsList()
    {
        $page = $this->request->post('page', 1);
        $pageSize = $this->request->post('pageSize', 20);
        $type = $this->request->post('type', 1);
        $params = $this->request->post();//筛选条件
        $action = $this->request->post('action', 'list'); // 新增：action参数，list或export
        
        // 使用统一的buildWhere方法构建查询条件
        $where = $this->buildWhere($params, $type);
   
        

        // 如果是导出操作
        if ($action === 'export') {
            return $this->handleExport($where, $params);
        }
        // 如果是删除操作
        if ($action === 'delete') {
            return $this->handleDelete($where, $params);
        }
        
        // 普通列表查询
        $list = Db::name('contracts a')
            ->join('customer c', 'a.customer_id = c.id', 'LEFT')
            ->join('user u', 'a.staff_id = u.id', 'LEFT')
            ->join('user us', 'a.user_id = us.id', 'LEFT')
            ->field('a.*, c.id as customerID,c.customerName, c.uidipin,u.name as staffName,u.id as staff_id,us.name as userName')
            ->where($where)
            ->order('a.id desc')
            ->page($page, $pageSize)
            ->select();

        foreach ($list as $key => $value) {
            $customer = ['id' => $value['customerID'], 'customerName' => $value['customerName'], 'uidipin' => $value['uidipin']];
            $staff = ['id' => $value['staff_id'], 'name' => $value['staffName']];
            $user = ['id' => $value['user_id'], 'name' => $value['userName']];
            $list[$key]['customer'] = $customer;
            $list[$key]['staff'] = $staff;
            $list[$key]['user'] = $user;
            $list[$key]['repaid_amount']=Db::name('payment_plan')->where('contract_id',$value['id'])->where('status','已回款')->where('is_delete',0)->sum('amount');
            $list[$key]['unrepaid_amount']=round($value['amount']-$list[$key]['repaid_amount'],2);
        }
        
        // 统计总数
        $total = Db::name('contracts a')
            ->join('customer c', 'a.customer_id = c.id', 'LEFT')
            ->join('user u', 'a.staff_id = u.id', 'LEFT')
            ->join('user us', 'a.user_id = us.id', 'LEFT')
            ->field('a.*, c.id as customerID,c.customerName, c.uidipin,u.name as staffName,u.id as staff_id,us.name as userName')
            ->where($where)
            ->count();


        $total_amount_find= Db::name('contracts a')
            ->join('customer c', 'a.customer_id = c.id', 'LEFT')
            ->join('user u', 'a.staff_id = u.id', 'LEFT')
            ->join('user us', 'a.user_id = us.id', 'LEFT')
            ->field('sum(a.amount) as total')
            ->where($where)
            ->find();
        $total_amount=$total_amount_find['total']?$total_amount_find['total']:0;

        $contractids=Db::name('contracts a')
            ->join('customer c', 'a.customer_id = c.id', 'LEFT')
            ->join('user u', 'a.staff_id = u.id', 'LEFT')
            ->join('user us', 'a.user_id = us.id', 'LEFT')
           
            ->where($where)
            ->column('a.id');

        $hashuikuan=Db::name('payment_record')
            ->where('contract_id','in',$contractids)
            ->field('sum(amount) as total')
            ->where('is_delete',0)
            ->find();
            
    
            
           
         
        $hashuikuan_amount=$hashuikuan['total']?$hashuikuan['total']:0;

        $weihuikuan_amount=round($total_amount-$hashuikuan_amount,2);

        $this->success('请求成功', ['list' => $list, 'total' => $total,'total_amount'=>$total_amount,'hashuikuan_amount'=>$hashuikuan_amount,'weihuikuan_amount'=>$weihuikuan_amount]);
    }
    
    
    /**
     * 首页
     *
     */
    public function createContracts()
    {
        $params = $this->request->post();

        // 验证必填字段
        if (empty($params['customer_id']) || empty($params['contract_name']) || empty($params['amount']) || empty($params['sign_date'])) {
            $this->error('缺少必填参数');
        }

        // 保存合同数据
        $contract = new ContractsModel();
        
        // 生成合同编号：先查询今日已创建的合同数量
        $today = date('Y-m-d');
        $todayCount = Db::name('contracts')
            ->where('DATE(created_at)', $today)
            ->where('is_delete', 0)
            ->count();
        $todayCount++; // 当前是第几个
        $contractNumber = str_pad($todayCount, 4, '0', STR_PAD_LEFT); // 不足四位前面补0
        
        $contractData = [
            'contractName' => $params['contract_name'],
            'customer_id' => $params['customer_id'],
            'contractCode' => 'BL-'.date('Ymd').'-'.$contractNumber,
            'amount' => $params['amount'],
            'sign_date' => $params['sign_date'],
            'manager' => $params['manager'] ?? '',
            'progress' => $params['progress'] ?? '',
            'remark' => $params['remark'] ?? '',
            'created_at'=>date('Y-m-d H:i:s'),
            'createtime'=>date('Y-m-d H:i:s'),
            'user_id'=>$this->auth->id,
            'staff_id'=>$params['staff_id'] ?? '',
        ];


        $contract->save($contractData);
        $contractId = $contract->id;


        // 处理还款计划
        if (!empty($params['repaymentPlans']) && is_array($params['repaymentPlans'])) {
            $paymentPlan = new \app\common\model\PaymentPlan();
            foreach ($params['repaymentPlans'] as $plan) {
                if (empty($plan['period']) || empty($plan['amount']) || empty($plan['date'])) {
                    continue;
                }

                $paymentPlan->create([
                    'period' => $plan['period'],
                    'customer_id' =>$params['customer_id'],
                    'amount' => $plan['amount'],
                    'date' => date('Y-m-d',strtotime($plan['date'])),
                    'method' => $plan['method'] ?? '',
                    'staff_id' => $this->auth->id,
                    'user_id' => $this->auth->id,
                    'contract_id' => $contractId,
                    'remark' => $plan['remark'] ?? '',
                    'createtime'=>date('Y-m-d H:i:s')
                ]);
            }
        }

        $this->success('合同创建成功');
    }

    public function getPlanList()
    {
        // 如果是 Selectpage 请求
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }
        // 查询条件
        $where = [];
        $id = $this->request->post('contractId', '');
        $where['contract_id'] = $id;
        $paymentPlan = new \app\common\model\PaymentPlan();
        $list = $paymentPlan
            ->where($where)
            ->order('id', 'desc')
            ->select();
        $this->success('请求成功', ['list' => $list]);
    }
    public function updateProgress(){
        $id = $this->request->post('contractId');
        $progress = $this->request->post('progress');
        $remark = $this->request->post('remark');
        $contracts = new ContractsModel();
        $param=['progress'=>$progress,];
        if($remark){
            $param['remark']=$remark;
        }
        $contracts->where('id', $id)->update($param);
        $this->success('更新成功');
    }
    public function deleteContracts(){

        $ids = $this->request->post('ids', '');
        $ids=explode(',',$ids);

        $find=Db::name('payment_plan')->where('contract_id','in',$ids)->where('is_delete',0)->find();
        if($find){
            $this->error('合同有回款计划，不能删除');
        }

        $contracts = new ContractsModel();
        $contracts->whereIn('id',$ids)->update(['is_delete'=>1]);
        $this->success();

    }

    public function processImport_old()
    {
        $fileUrl = $this->request->post('fileUrl');
        $sheetName = $this->request->post('sheetName');

        if (empty($fileUrl) || empty($sheetName)) {
            $this->error('文件URL和Sheet名称不能为空');
        }

        // FastAdmin 的文件存储通常在 public/uploads/
        // 这里假设 $fileUrl 是从 FastAdmin 的上传接口返回的相对路径，例如 /uploads/20250617/xxx.xls
        $localFilePath = ROOT_PATH . 'public' . str_replace('/', DS, $fileUrl);


        if (!file_exists($localFilePath)) {
            $this->error('服务器上找不到文件，请确认文件已正确上传路径：' . $localFilePath);
        }

        $importedCount = 0;
        $updatedCount = 0;
        $skippedCount = 0;
        $failedCount = 0;
        $errorMessages = [];
        $customerModel = new CustomerModel();

        $spreadsheet = IOFactory::load($localFilePath);
        $sheet = $spreadsheet->getSheetByName($sheetName);

        if (!$sheet) {
            $this->error('指定的Sheet名称不存在：' . $sheetName);
        }

        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn(); // 比如 'BO'
        $maxColIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);

        $header = [];
        for ($i = 1; $i <= $maxColIndex; $i++) {
            $header[] = trim($sheet->getCellByColumnAndRow($i, 1)->getValue());
        }

        // 定义 Excel 表头到数据库字段的映射 (根据 exportExcel 的 fieldMap )
        $excelToDbMap = [
            '回款协议编号'   => 'contractCode',
            '回款协议名称'   => 'contractName',
            '所属案源客户'   => 'customer_id',
            'UID/PIN'      => 'uidipin', // 如有
            '协议回款总金额' => 'amount',
            '签订协议时间'   => 'sign_date',
            '协议负责人'     => 'manager',
            '回款回函证据上传' => 'upload_url',
            '进度'         => 'progress',
            '备注'         => 'remark',
            '证据'         => 'evidence',
            '审核状态'         => 'progress',
            // 其它字段...
        ];
        // 缓存用户名称到ID的映射，避免循环查询
        $userCache = [];
        for ($rowNum = 2; $rowNum <= $highestRow; $rowNum++) {
            $rowData = [];
            for ($i = 1; $i <= $maxColIndex; $i++) {
                $headerIndex = $i - 1;
                if (!isset($header[$headerIndex])) continue;
                $headerName = $header[$headerIndex];
                $cellValue = $sheet->getCellByColumnAndRow($i, $rowNum)->getValue();
                if (isset($excelToDbMap[$headerName])) {
                    $dbField = $excelToDbMap[$headerName];
                    $rowData[$dbField] = trim($cellValue);
                }
            }
            // 字段类型处理
            if (isset($rowData['amount'])) $rowData['amount'] = floatval($rowData['amount']);
            if (isset($rowData['sign_date'])) {
                if (is_numeric($rowData['sign_date'])) {
                    $rowData['sign_date'] = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($rowData['sign_date'])->format('Y-m-d');
                }
            }


            // 默认值
            $rowData['status'] = 0;
            $rowData['is_delete'] = 0;
            $rowData['created_at'] = date('Y-m-d H:i:s');
            $rowData['updated_at'] = date('Y-m-d H:i:s');

            $importedCount++;
            // 插入
            $contractModel = new ContractsModel();
            $contractModel->save($rowData);
        }
        Db::name('import_log')->insert(
            [
                'success'=>$importedCount,
                'fail'=>$failedCount,
                'url'=>$fileUrl,
                'name'=>$sheetName,
                'type'=>3,
                'user_id'=>$this->auth->id,
                'createtime'=>date('Y-m-d H:i:s'),
            ]
        );
        $this->success('导入完成', [
            'imported' => $importedCount,
            'updated' => $updatedCount,
            'skipped' => $skippedCount,
            'failed' => $failedCount,
            'errors' => $errorMessages
        ]);

    }
    public function processImport()
    {
        $fileUrl = $this->request->post('fileUrl');
        $sheetName = $this->request->post('sheetName');
        $fieldMappings = $this->request->post('fieldMappings/a', []); // 前端传的映射关系
        $responsiblePersonType = $this->request->post('responsiblePersonType', 'excel'); // 负责人类型
        $responsibleUserId = $this->request->post('responsibleUserId', 0); // 指定负责人ID

        if (empty($fileUrl) || empty($sheetName) || empty($fieldMappings)) {
            $this->error('文件URL、Sheet名称和字段映射不能为空');
        }

        $localFilePath = ROOT_PATH . 'public' . str_replace('/', DS, $fileUrl);
        if (!file_exists($localFilePath)) {
            $this->error('服务器上找不到文件，请确认文件已正确上传路径：' . $localFilePath);
        }

        $importedCount = 0;
        $updatedCount = 0;
        $skippedCount = 0;
        $failedCount = 0;
        $errorMessages = [];

        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($localFilePath);
        $sheet = $spreadsheet->getSheetByName($sheetName);
        if (!$sheet) {
            $this->error('指定的Sheet名称不存在：' . $sheetName);
        }

        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();
        $maxColIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);

        // 1. 生成 Excel列名 => 列号 的映射
        $excelHeaderMap = [];
        for ($i = 1; $i <= $maxColIndex; $i++) {
            $headerName = trim($sheet->getCellByColumnAndRow($i, 1)->getValue());
            if ($headerName !== '') {
                $excelHeaderMap[$headerName] = $i;
            }
        }

        // 2. 生成 系统字段 => [excel列名, 默认值, 列号] 的映射
        $fieldMap = [];
        foreach ($fieldMappings as $map) {
            $sysField = $map['systemField'];
            $excelField = $map['excelField'];
            $defaultValue = $map['defaultValue'];
            $colIndex = isset($excelHeaderMap[$excelField]) ? $excelHeaderMap[$excelField] : null;
            $fieldMap[$sysField] = [
                'excelField' => $excelField,
                'defaultValue' => $defaultValue,
                'colIndex' => $colIndex
            ];
        }

        $customerModel = new CustomerModel();


        for ($rowNum = 2; $rowNum <= $highestRow; $rowNum++) { // 从第二行开始
            $rowData = [];
            $rowError = [];

            // 3. 按系统字段组装数据
            foreach ($fieldMap as $sysField => $info) {


                $value = '';
                if ($info['colIndex']) {
                    $cellValue = $sheet->getCellByColumnAndRow($info['colIndex'], $rowNum)->getValue();
                    $value = trim($cellValue);
                }
                if ($value === '' && $info['defaultValue'] !== '') {
                    $value = $info['defaultValue'];
                }

                // 特殊处理日期/时间字段
                if ($value === '' || $value === null) {
                    $value = null; // 空值设为 null
                } else {
                    if($sysField=='customer_id'){
                        $customer=$customerModel->where('customerName',$value)->where('is_delete',0)->find();
                        if($customer){
                            $value = $customer['id'];
                        }else{
                            $value='';
                        }
                    }
                    if($sysField=='staff_id'){
                        if($responsiblePersonType == 'excel'){
                            // 从Excel中获取负责人
                            $userModel=new User();
                            $user=$userModel->where('name',$value)->where('is_delete',0)->find();
                            if($user){
                                $value = $user['id'];
                            }else{
                                $value = null; // 设置为 null，在外层统一处理
                            }
                        }elseif($responsiblePersonType == 'assign'){
                            // 使用指定的负责人ID
                            $value = $responsibleUserId;
                        }else{
                            $value = null;
                        }
                    }
                }
                $rowData[$sysField] = $value;
            }

            unset($rowData['uidipin']);
            if (empty($rowData) || !array_filter($rowData)) {
                continue;
            }
            //创建人
            $rowData['user_id'] = $this->auth->id;

            // 验证 customer_id 和 staff_id 是否为空
            $validationErrors = [];
            if (empty($rowData['customer_id'])) {
                $validationErrors[] = "客户为空";
            }
            if (empty($rowData['staff_id'])) {
                $validationErrors[] = "负责人为空";
            }

            if (!empty($validationErrors)) {
                $failedCount++;
                $errorMessages[] = "第 {$rowNum} 行：" . implode('、', $validationErrors) . "，跳过此条数据";
                continue;
            }


            $contractsModel=new ContractsModel();
            $res = $contractsModel->save($rowData);
            if ($res !== false) {
                $importedCount++;
            } else {
                $failedCount++;
                $rowError[] = "插入失败（" . ($contractsModel->getError() ?: '未知错误') . "）。";
                $errorMessages[] = "第 {$rowNum} 行：" . implode(' ', $rowError);
            }

        }

        Db::name('import_log')->insert([
            'success' => $importedCount,
            'fail' => $failedCount,
            'url' => $fileUrl,
            'name' => $sheetName,
            'type' => 3,
            'total' => $importedCount + $failedCount,
            'user_id' => $this->auth->id,
            'createtime' => date('Y-m-d H:i:s'),
        ]);

        $this->success('导入完成', [
            'imported' => $importedCount,
            'updated' => $updatedCount,
            'skipped' => $skippedCount,
            'failed' => $failedCount,
            'errors' => $errorMessages
        ]);
    }
    public function importlog()
    {
        // 获取分页参数
        $page = $this->request->post('page', 1);
        $pageSize = $this->request->post('pageSize', 20);

        // 查询条件
        $where = [];
        $userId = $this->auth->id;
        $where['type']=3;
        $where['user_id']=$userId;

        $importlog = new \app\common\model\Importlog();
        $total = $importlog->where($where)->count();
        $list = $importlog
            ->with(['user' => function($query) {
                $query->field('id,name');
            }])
            ->where($where)
            ->order('id', 'desc')
            ->page($page, $pageSize)
            ->select();

        $request = \think\Request::instance();
        $domain = $request->domain();
        foreach ($list as $k => $v) {
            $list[$k]['url'] = $domain . $v['url'];
        }
        $this->success('请求成功', ['list' => $list, 'total' => $total]);
    }
    //选择合同弹框
    public function getSelectContracts()
    {
        // 获取分页参数
        $page = $this->request->post('page', 1);
        $pageSize = $this->request->post('pageSize', 20);
        $customerId = $this->request->post('customerId', 'customerId');
        // 查询条件
        $where = [];
        $where['is_delete']=0;
        $searchKey = $this->request->post('searchKey', '');
        if ($searchKey) {
            $where['name|customer_name|phone|manager'] = ['like', "%{$searchKey}%"];
        }
        if(!empty($customerId)){
            $where['customer_id'] = $customerId;
        }
        $contracts = new \app\common\model\Contracts();
        $total = $contracts->where($where)->count();
        $list = $contracts
            ->where($where)
            ->with(['customer' => function($query) {
                $query->field('id,customerName,uidipin');
            }])
            ->with(['staff' => function($query) {
                $query->field('id,name');
            }])
            ->order('id', 'desc')
            ->page($page, $pageSize)
            ->select();

        foreach ($list as $key => $value) {
            $list[$key]['unrepaid_amount']=round($value['amount']-$value['repaid_amount'],2);
        }

        $this->success('请求成功', ['list' => $list, 'total' => $total]);
    }

    /**
     * 构建查询条件
     * @param array $params 查询参数
     * @param int $type 查询类型
     * @return array
     */
    private function buildWhere($params, $type)
    {
        $where = [];
        $where['a.is_delete'] = 0;
        // 数据权限
        $userPermissionService = new UserPermissionService();
        $staffids=$userPermissionService->getUsersByPermission($this->auth->id);
        $where['a.staff_id']=['in',$staffids];
        

        $searchKey = isset($params['searchKey']) ? $params['searchKey'] : '';
        if ($searchKey) {
            $where['c.customerName|c.phone|c.uidipin'] = ['like', "%{$searchKey}%"];
        }
        if (isset($params['customerName']) && !empty($params['customerName'])) {
            $where['c.customerName'] = $params['customerName'];
        }
        if (isset($params['uidipin']) && !empty($params['uidipin'])) {
            $where['c.uidipin'] = $params['uidipin'];
        }
        if (isset($params['contractCode']) && !empty($params['contractCode'])) {
            $where['a.contractCode'] = $params['contractCode'];
        }
        if (isset($params['contractName']) && !empty($params['contractName'])) {
            $where['a.contractName'] = ['like', "%{$params['contractName']}%"];
        }
        if (isset($params['progress']) && !empty($params['progress'])) {
            $where['a.progress'] = $params['progress'];
        }
        if (isset($params['staff_id']) && !empty($params['staff_id'])) {
            $where['c.staff_id'] = $params['staff_id']; 
        }
  
        
       if (isset($params['department_id']) && !empty($params['department_id'])) {
            // 获取这些部门的所有用户ID
            $userIds = $this->getDepartmentUserIds($params['department_id']);
            // 与数据权限的staffids取交集
            $where['c.staff_id'] = ['in', array_intersect($userIds, $staffids)];
        } 


        
        
        switch ($type) {
            case 1:
                break;
            case 2:
                $where['a.staff_id'] = $this->auth->id;
                break;
            case 3:
                $staffids = Db::name('user')->where('leader_id', $this->auth->id)->column('id');
                $where['a.staff_id'] = ['in', $staffids];
                break;
            default:
                break;
        }
        return $where;
    }

    /**
     * 处理导出功能
     * @param array $where 查询条件
     * @param array $params 请求参数
     * @return \think\response\Json
     */
    private function handleExport($where, $params)
    {
        // 获取导出参数
        $export_type = $this->request->post('export_type', 'all'); // 导出类型：selected, currentPage, all
        $fields = $this->request->post('fields'); // 选中的字段
        $selectedIds = $this->request->post('ids', []); // 选中的ID列表

        // 如果没有选择字段，返回错误
        if (empty($fields)) {
            $this->error('请选择要导出的字段');
        }
        $fields = explode(',', $fields);

        // 根据导出类型构建查询
        $contracts = new ContractsModel();
        switch ($export_type) {
            case 'selected':
                if (empty($selectedIds)) {
                    $this->error('请选择要导出的数据');
                }
                $list = $contracts->alias('a')
                    ->join('customer c', 'a.customer_id = c.id', 'LEFT')
                    ->field('a.*, c.customerName, c.uidipin')
                    ->where($where)
                    ->whereIn('a.id', $selectedIds)
                    ->select();
                break;
            case 'currentPage':
                $list = $contracts->alias('a')
                    ->join('customer c', 'a.customer_id = c.id', 'LEFT')
                    ->field('a.*, c.customerName, c.uidipin')
                    ->where($where)
                    ->whereIn('a.id', $selectedIds)
                    ->select();
                break;
            case 'all':
            default:
                $list = $contracts->alias('a')
                    ->join('customer c', 'a.customer_id = c.id', 'LEFT')
                    ->field('a.*, c.customerName,c.uidipin')
                    ->where($where)
                    ->order('a.id', 'desc')
                    ->select();
                break;
        }

        // 创建新的 Spreadsheet 对象
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 字段映射表 - 按照图片中的列顺序排列
        $fieldMap = [
            'contractName' => '回款协议名称',
            'contractCode' => '回款协议编号',
            'customerName' => '所属案源客户',  // 前端传的字段名
            'uidipin' => '回款客户UID/PIN',
            'amount' => '协议回款总金额',
            'sign_date' => '签订协议时间',
            'manager' => '协议负责人',
            'upload_url' => '回款回函证据上传',  // 前端传的字段名
            'progress' => '订单进度',
            'status' => '审核状态',
            'staff_id' => '负责人',
            'dept_id' => '部门',
            'created_at' => '创建时间',
            'updated_at' => '更新时间',
            'paid_amount' => '已回款金额',
            'unpaid_amount' => '未回款金额',
            'invoice_amount' => '发票金额',
            'expense_amount' => '费用金额',
            'total_cost' => '成本总额',
            'total_profit' => '利润总额',
            'last_follow_time' => '最后跟进时间',
            'next_follow_time' => '下次跟进时间',
            'latest_follow_content' => '最新跟进内容'
        ];

        // 写入表头
        $col = 1;
        foreach ($fields as $field) {
            if (isset($fieldMap[$field])) {
                $sheet->setCellValueByColumnAndRow($col, 1, $fieldMap[$field]);
                $col++;
            }
        }
        
        // 写入数据
        $row = 2;
        foreach ($list as $item) {
            $col = 1;
            foreach ($fields as $field) {
                $value = $item[$field] ?? '';

                // 处理特殊字段
                switch ($field) {
                    case 'staff_id':
                        // 关联用户表获取员工姓名
                        if ($value) {
                            $user = Db::name('user')->where('id', $value)->field('name')->find();
                            $value = $user ? $user['name'] : '';
                        }
                        break;
                    case 'dept_id':
                        // 关联部门表获取部门名称
                        if ($value) {
                            $dept = Db::name('department')->where('id', $value)->field('title')->find();
                            $value = $dept ? $dept['title'] : '';
                        }
                        break;
                    case 'uidipin':
                        // 从关联的客户数据中获取
                        $value = $item['uidipin'] ?? '';
                        break;
                    case 'customerName':
                        // 从关联的客户数据中获取客户名称
                        $value = $item['customerName'] ?? '';
                        break;
                    case 'upload_url':
                        // 回款回函证据上传
                        $value = $item['upload_url'] ?? '';
                        break;
                    case 'status':
                        // 审核状态处理
                        $value = $value == 1 ? '审核通过' : '待审核';
                        break;
                    case 'created_at':
                    case 'updated_at':
                        $value = $value ? date('Y-m-d H:i:s', strtotime($value)) : '';
                        break;
                    case 'sign_date':
                        $value = $value ? date('Y-m-d', strtotime($value)) : '';
                        break;
                    case 'paid_amount':
                    case 'unpaid_amount':
                    case 'invoice_amount':
                    case 'expense_amount':
                    case 'total_cost':
                    case 'total_profit':
                    case 'last_follow_time':
                    case 'next_follow_time':
                    case 'latest_follow_content':
                        // 这些字段在数据表中不存在，返回空值
                        $value = '';
                        break;
                    case 'manager':
                        // 协议负责人 - 数据表中标记为废弃，返回空值
                        $value = '';
                        break;
                    case 'communicationStatus':
                    case 'lawsuitStatus':
                        $value = is_string($value) ? $value : json_encode($value, JSON_UNESCAPED_UNICODE);
                        break;
                }

                $sheet->setCellValueByColumnAndRow($col, $row, $value);
                $col++;
            }
            $row++;
        }

        // 设置列宽
        for ($i = 1; $i <= count($fields); $i++) {
            $sheet->getColumnDimensionByColumn($i)->setWidth(30);
        }

        // 设置表头样式
        $headerStyle = [
            'font' => ['bold' => true],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
            ],
            'borders' => [
                'allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]
            ],
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                'startColor' => ['rgb' => 'E0E0E0']
            ]
        ];
        $sheet->getStyle('A1:' . $sheet->getHighestColumn() . '1')->applyFromArray($headerStyle);

        // 设置数据区域样式
        $dataStyle = [
            'borders' => [
                'allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
            ]
        ];
        $sheet->getStyle('A2:' . $sheet->getHighestColumn() . ($row - 1))->applyFromArray($dataStyle);

        // 生成文件名
        $filename = '合同数据_' . date('YmdHis') . '.xlsx';

        // 确保目录存在
        $savePath = ROOT_PATH . 'public' . DS . 'uploads' . DS . 'excel';
        if (!is_dir($savePath)) {
            mkdir($savePath, 0755, true);
        }

        // 完整的文件保存路径
        $filePath = $savePath . DS . $filename;

        // 保存文件
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($filePath);

        // 返回文件URL给前端
        $request = Request::instance();
        $domain = $request->domain();
        $fileUrl = $domain . '/uploads/excel/' . $filename;
        $this->success('导出成功', ['url' => $fileUrl]);
    }

    /**
     * 处理删除功能（按条件删除，不传 ids 时按 where 条件删除）
     * @param array $where
     * @param array $params
     * @return \think\response\Json
     */
    private function handleDelete($where, $params)
    {
        // 使用与列表相同的关联查询获取 a.id 列表
        $ids = Db::name('contracts a')
            ->join('customer c', 'a.customer_id = c.id', 'LEFT')
            ->join('user u', 'a.staff_id = u.id', 'LEFT')
            ->join('user us', 'a.user_id = us.id', 'LEFT')
            ->where($where)
            ->column('a.id');

        if (empty($ids)) {
            $this->error('没有找到需要删除的记录');
        }

        $find=Db::name('payment_plan')->where('contract_id','in',$ids)->where('is_delete',0)->find();
        if($find){
            $this->error('合同有回款计划，不能删除');
        }

        // 按基础表ID软删除，避免别名歧义
        $result = Db::name('contracts')
            ->where('id', 'in', $ids)
            ->update([
                'is_delete' => 1,
                'deletetime' => time(),
            ]);

        $deletedCount = $result ?: 0;
        $this->success("删除成功，共删除 {$deletedCount} 条记录");
    }


}